package com.sinaapp.zygl.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.sinaapp.zygl.bean.Business;
import com.sinaapp.zygl.bean.UserAndCustomer;
import com.sinaapp.zygl.dao.BusinessDao;
import com.sinaapp.zygl.util.DbUtil;

public class BusinessDaoImpl implements BusinessDao {
	
	/* b_id uid,cid,b_business,b_evaluate */
	/* uid;name;cid;cname;b_business;b_evaluate; */
	@Override
	public boolean add(Business b) {
		Connection conn = DbUtil.getConnection();
		String sql = "insert into business(uid,cid,b_business,b_evaluate) values(?,?,?,?)";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, b.getUid());
			pstmt.setString(2, b.getCid());
			pstmt.setString(3, b.getB_business());
			pstmt.setString(4, b.getB_evaluate());
			pstmt.executeUpdate();
			return true;
			// System.out.println("add success");
		} catch (SQLException e) {
			System.out.println("add false!");
		} finally {
			DbUtil.closeConnection(conn);
		}
		return false;
	}

	@Override
	public boolean delete(int bid) {
		Connection conn = DbUtil.getConnection();
		try {
			String sql = "delete from business where b_id=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bid);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return false;
	}

	@Override
	public boolean update(Business b) {
		Connection conn = DbUtil.getConnection();
		String sql = "update business set uid=?,cid=?,b_business=?,b_evaluate=? where b_id=? ";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, b.getUid());
			pstmt.setString(2, b.getCid());
			pstmt.setString(3, b.getB_business());
			pstmt.setString(4, b.getB_evaluate());
			pstmt.setInt(5, b.getB_id());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return false;
	}

	@Override
	public int queryBusinessTotal(Business b) {
		Connection conn = DbUtil.getConnection();
		String sql = addSqlStringB("select count(b_id) FROM business", b);
		int total = 0;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
		} finally {
			DbUtil.closeConnection(conn);
		}
		return total;
	}

	@Override
	public List<Business> getBusinesss(int page, int rows, String sort, String order, Business b) {
		Connection conn = DbUtil.getConnection();
		List<Business> queryCtmlist = new ArrayList<Business>();
		try {
			String sql1 = "";
			String sql2 = "";
			if ((sort == null || sort.equals("")) | (order == null || order.equals(""))) {
				sql2 = addSqlStringB("select * FROM business", b) + " ORDER BY b_id asc LIMIT 0,0";
				// System.out.println(sql2 + " "+ DateUtil.dateToString(new
				// Date(), "yyyy-MM-dd HH:mm:ss"));
			} else {
				sql1 = " " + ((page - 1) * rows) + "," + rows + "";
				// select * FROM Customer ORDER BY id asc LIMIT 5,10
				sql2 = addSqlStringB("select * from business", b) + " order by " + sort + " "
						+ order + " " + "limit" + sql1;
				// System.out.println(sql2 + " "+ DateUtil.dateToString(new
				// Date(), "yyyy-MM-dd HH:mm:ss"));
			}
			PreparedStatement pstmt = conn.prepareStatement(sql2);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Business b1 = new Business();
				b1.setB_id(rs.getInt(1));
				b1.setUid(rs.getString(2));
				b1.setCid(rs.getString(3));
				b1.setB_business(rs.getString(4));
				b1.setB_evaluate(rs.getString(5));
				queryCtmlist.add(b1);
			}
			return queryCtmlist;
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return null;
	}

	@Override
	public int queryUserAndCustomerTotal(UserAndCustomer uac) {
		Connection conn = DbUtil.getConnection();
		String sql = addSqlStringUac("select count(uid) FROM v_show_business", uac);
		int total = 0;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
		} finally {
			DbUtil.closeConnection(conn);
		}
		return total;
	}

	@Override
	public List<UserAndCustomer> getUserAndCustomer(int page, int rows, String sort, String order,
			UserAndCustomer uac) {
		Connection conn = DbUtil.getConnection();
		List<UserAndCustomer> queryCtmlist = new ArrayList<UserAndCustomer>();
		try {
			String sql1 = "";
			String sql2 = "";
			if ((sort == null || sort.equals("")) | (order == null || order.equals(""))) {
				sql2 = addSqlStringUac("select * FROM v_show_business", uac)
						+ " ORDER BY uid asc LIMIT 0,0";
				// System.out.println(sql2 + " "+ DateUtil.dateToString(new
				// Date(), "yyyy-MM-dd HH:mm:ss"));
			} else {
				sql1 = " " + ((page - 1) * rows) + "," + rows + "";
				// select * FROM Customer ORDER BY id asc LIMIT 5,10
				sql2 = addSqlStringUac("select * from v_show_business", uac) + " order by " + sort
						+ " " + order + " " + "limit" + sql1;
				// System.out.println(sql2 + " "+ DateUtil.dateToString(new
				// Date(), "yyyy-MM-dd HH:mm:ss"));
			}
			PreparedStatement pstmt = conn.prepareStatement(sql2);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				UserAndCustomer b1 = new UserAndCustomer();
				b1.setUid(rs.getString(1));
				b1.setName(rs.getString(2));
				b1.setCid(rs.getString(3));
				b1.setCname(rs.getString(4));
				b1.setB_business(rs.getString(5));
				b1.setB_evaluate(rs.getString(6));
				queryCtmlist.add(b1);
			}
			return queryCtmlist;
		} catch (SQLException ex) {
			Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			DbUtil.closeConnection(conn);
		}
		return null;
	}

	private String addSqlStringB(String input, Business b) {
		String sql = input;
		// b_id uid,cid,b_business,b_evaluate
		if ((b.getB_id() > 0) && ("" + b.getB_id()) != null) {
			// select * FROM user WHERE id ='5' ORDER BY id asc LIMIT 0,10
			sql += " where b_id=" + b.getB_id() + "";
			return sql;
		}
		if (b.getUid() != null && b.getUid().length() != 0) {
			// select * FROM user WHERE name like '土%%'or name like '%土%' ORDER
			// BY id asc LIMIT 0,10
			sql += " where uid like '" + b.getCid() + "%%' or uid like " + "'%" + b.getUid()
					+ "%' ";
			return sql;
		}
		if (b.getCid() != null && b.getCid().length() != 0) {
			sql += " where cid like '" + b.getCid() + "%%' or cid like " + "'%" + b.getCid()
					+ "%' ";
			return sql;
		}
		if (b.getB_business() != null && b.getB_business().length() != 0) {
			sql += " where b_business like '" + b.getB_business() + "%%' or b_business like "
					+ "'%" + b.getB_business() + "%' ";
			return sql;
		}
		if (b.getB_evaluate() != null && b.getB_evaluate().length() != 0) {
			sql += " where b_evaluate like '" + b.getB_evaluate() + "%%' or b_evaluate like "
					+ "'%" + b.getB_evaluate() + "%' ";
			return sql;
		}
		return sql;
	}

	private String addSqlStringUac(String input, UserAndCustomer uac) {
		String sql = input;
		// uid,name cid,cname b_business,b_evaluate
		if (uac.getUid() != null && uac.getUid().length() != 0) {
			// select * FROM user WHERE name like '土%%'or name like '%土%' ORDER
			// BY id asc LIMIT 0,10
			sql += " where uid like '" + uac.getCid() + "%%' or uid like " + "'%" + uac.getUid()
					+ "%' ";
			return sql;
		}
		if (uac.getName() != null && uac.getName().length() != 0) {
			sql += " where name like '" + uac.getName() + "%%' or name like " + "'%"
					+ uac.getName() + "%' ";
			return sql;
		}
		if (uac.getCid() != null && uac.getCid().length() != 0) {
			sql += " where cid like '" + uac.getCid() + "%%' or cid like " + "'%" + uac.getCid()
					+ "%' ";
			return sql;
		}
		if (uac.getCname() != null && uac.getCname().length() != 0) {
			sql += " where cname like '" + uac.getCname() + "%%' or cname like " + "'%"
					+ uac.getCname() + "%' ";
			return sql;
		}
		if (uac.getB_business() != null && uac.getB_business().length() != 0) {
			sql += " where b_business like '" + uac.getB_business() + "%%' or b_business like "
					+ "'%" + uac.getB_business() + "%' ";
			return sql;
		}
		if (uac.getB_evaluate() != null && uac.getB_evaluate().length() != 0) {
			sql += " where b_evaluate like '" + uac.getB_evaluate() + "%%' or b_evaluate like "
					+ "'%" + uac.getB_evaluate() + "%' ";
			return sql;
		}
		return sql;
	}
}
